﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using XExcel.Models;

namespace XExcel
{
    /// <summary>
    /// excel操作工具类
    /// </summary>
    public static class ExcelHelper
    {
        #region IEnumerable<T> 

        /// <summary>
        /// 保存数据列表到Excel文件，版本格式根据文件名后缀判断
        /// </summary>
        /// <typeparam name="T">集合数据类型</typeparam>
        /// <param name="data">数据列表</param>
        /// <param name="fileName">保存文件名</param>
        /// <param name="mapper">列名映射表，键：列名，值：属性名</param>
        /// <param name="title">标题</param>
        /// <param name="callback">回调函数</param>
        public static string SaveToExcel<T>(IEnumerable<T> data, string fileName, IDictionary<string, string> mapper, string title = null, Action<SheetInfo> callback = null, byte skipRow = 0)
        {
            var version = fileName.EndsWith(".xls", StringComparison.OrdinalIgnoreCase) ? ExcelVersion.Xls : ExcelVersion.Xlsx;
            var book = SaveToWorkbook(data, mapper, version, title, callback, skipRow);
            data = null;
            mapper = null;
            callback = null;
            return SaveBook(fileName, book);
        }

        /// <summary>
        /// 将集合数据保存到Excel流中
        /// </summary>
        /// <typeparam name="T">集合数据类型</typeparam>
        /// <param name="data">数据</param>
        /// <param name="mapper">列名映射表，键：列名，值：属性名</param>
        /// <param name="version">excel版本</param>
        /// <param name="title">标题</param>
        /// <param name="callback">回调函数</param>
        /// <returns></returns>
        public static MemoryStream SaveToExcel<T>(IEnumerable<T> data, IDictionary<string, string> mapper, ExcelVersion version = ExcelVersion.Xlsx, string title = null, Action<SheetInfo> callback = null, byte skipRow = 0)
        {
            var book = SaveToWorkbook(data, mapper, version, title, callback, skipRow);
            data = null;
            mapper = null;
            callback = null;
            return SaveBook(book);
        }

        private static IWorkbook SaveToWorkbook<T>(IEnumerable<T> data, IDictionary<string, string> mapper, ExcelVersion version = ExcelVersion.Xlsx, string title = null, Action<SheetInfo> callback = null, byte skipRow = 0)
        {
            var enumerable = data?.ToList();
            if(enumerable == null || !enumerable.Any())
            {
                throw new IOException("数据不存在");
            }
            IWorkbook book = CreateWorkbook(version);

            var type = typeof(T);
            var pis = type.GetProperties();
            if(!pis.Any())
            {
                throw new IOException("不存在可以导出的属性");
            }
            ISheet sheet = book.CreateSheet(type.Name);
            var mappers = new List<Mapper>();
            if(mapper == null)
            {
                foreach(var pi in pis)
                {
                    mappers.Add(new Mapper
                    {
                        Type = pi.PropertyType,
                        Name = pi.Name,
                        ColumnName = pi.Name,
                        Property = pi
                    });
                }
            }
            else
            {
                foreach(var pair in mapper)
                {
                    var pi = pis.FirstOrDefault(x => x.Name == pair.Value);
                    if(pi != null)
                    {
                        mappers.Add(new Mapper
                        {
                            Type = pi.PropertyType,
                            Name = pi.Name,
                            ColumnName = pair.Key,
                            Property = pi
                        });
                    }
                }
            }

            DataAction action = (ISheet _sheet, ref int rowIndex, ICellStyle styleCell) =>
            {
                foreach(var item in enumerable)
                {
                    if(item == null)
                    {
                        continue;
                    }
                    int colIndex = 0;
                    IRow row = _sheet.CreateRow(rowIndex);
                    foreach(var pi in mappers)
                    {
                        ICell cell = row.CreateCell(colIndex);
                        cell.CellStyle = styleCell;
                        var val = pi.Property.GetValue(item);
                        if(val == null)
                        {
                        }
                        else if(pi.Type == typeof(bool))
                        {
                            cell.SetCellValue(Convert.ToBoolean(val));
                        }
                        else if(pi.Type == typeof(DateTime))
                        {
                            cell.SetCellValue(Convert.ToDateTime(val));
                        }
                        else if(IsDoubleType(pi.Type))
                        {
                            cell.SetCellValue(Convert.ToDouble(val));
                        }
                        else
                        {
                            cell.SetCellValue(val.ToString());
                        }
                        colIndex++;
                    }

                    rowIndex++;
                }
            };

            SaveToSheet(action, sheet, mapper, title, callback, skipRow);

            GC.Collect();
            return book;
        }

        #endregion

        #region DataTable

        /// <summary>
        /// 将DataTable数据保存到Excel文件，版本格式根据文件名后缀判断
        /// </summary>
        /// <param name="table">表</param>
        /// <param name="fileName">保存文件名</param>
        /// <param name="title">标题</param>
        /// <param name="callback">回调函数</param>
        /// <returns></returns>
        public static string SaveToExcel(DataTable table, string fileName, IDictionary<string, string> mapper, string title = null, Action<SheetInfo> callback = null, byte skipRow = 0)
        {
            var version = fileName.EndsWith(".xls", StringComparison.OrdinalIgnoreCase) ? ExcelVersion.Xls : ExcelVersion.Xlsx;
            var book = SaveToWorkbook(table, mapper, version, title, callback, skipRow);
            table.Dispose();
            table = null;
            mapper = null;
            callback = null;
            return SaveBook(fileName, book);
        }

        /// <summary>
        /// 将DataTable数据保存到Excel流中
        /// </summary>
        /// <param name="table">表</param>
        /// <param name="version">excel版本</param>
        /// <param name="title">标题</param>
        /// <param name="callback">回调函数</param>
        /// <returns></returns>
        public static MemoryStream SaveToExcel(DataTable table, IDictionary<string, string> mapper, ExcelVersion version = ExcelVersion.Xlsx, string title = null, Action<SheetInfo> callback = null, byte skipRow = 0)
        {
            var book = SaveToWorkbook(table, mapper, version, title, callback, skipRow);
            table.Dispose();
            table = null;
            mapper = null;
            callback = null;
            return SaveBook(book);
        }

        private static IWorkbook SaveToWorkbook(DataTable table, IDictionary<string, string> mapper, ExcelVersion version = ExcelVersion.Xlsx, string title = null, Action<SheetInfo> callback = null, byte skipRow = 0)
        {
            if(table == null)
            {
                throw new IOException("数据表不存在");
            }

            if(table.Columns.Count == 0)
            {
                throw new IOException("不存在可以导出的列");
            }
            if(table.Rows.Count == 0)
            {
                throw new IOException("不存在可以导出的行");
            }

            IWorkbook book = CreateWorkbook(version);
            var tableName = string.IsNullOrWhiteSpace(table.TableName) ? "sheet1" : table.TableName;
            ISheet sheet = book.CreateSheet(tableName);
            SaveToSheet(table, sheet, mapper, title, callback, skipRow);
            GC.Collect();
            return book;
        }

        private static void SaveToSheet(DataTable table, ISheet sheet, IDictionary<string, string> mapper, string title = null, Action<SheetInfo> callback = null, byte skipRow = 0)
        {
            if(mapper == null || mapper.Count == 0)
            {
                mapper = table.Columns.OfType<DataColumn>().ToDictionary(x => x.ColumnName, x => x.Caption);
            }
            else
            {
                mapper = mapper.Where(x => table.Columns.Contains(x.Key)).ToDictionary(x => x.Key, x => x.Value);
            }

            DataAction action = (ISheet _sheet, ref int rowIndex, ICellStyle styleCell) =>
            {
                foreach(DataRow dtRow in table.Rows)
                {
                    if(dtRow == null)
                    {
                        continue;
                    }
                    int colIndex = 0;
                    IRow row = _sheet.CreateRow(rowIndex);
                    foreach(var key in mapper.Keys)
                    {
                        var item = table.Columns[key];
                        var cell = row.CreateCell(colIndex);
                        cell.CellStyle = styleCell;
                        var val = dtRow[item];
                        if(val == null || val.Equals(DBNull.Value))
                        {
                        }
                        else if(item.DataType == typeof(bool))
                        {
                            cell.SetCellValue((bool)val);
                        }
                        else if(item.DataType == typeof(DateTime))
                        {
                            cell.SetCellValue((DateTime)val);
                        }
                        else if(IsDoubleType(item.DataType))
                        {
                            cell.SetCellValue(Convert.ToDouble(val));
                        }
                        else
                        {
                            cell.SetCellValue(val.ToString());
                        }
                        colIndex++;
                    }

                    rowIndex++;
                }
            };

            SaveToSheet(action, sheet, mapper, title, callback, skipRow);
        }

        private static void SaveToSheet(DataAction action, ISheet sheet, IDictionary<string, string> mapper, string title, Action<SheetInfo> callback, byte skipRow)
        {
            var styleCell = sheet.Workbook.CreateCellStyle();
            styleCell.BorderBottom = BorderStyle.Thin;
            styleCell.BorderLeft = BorderStyle.Thin;
            styleCell.BorderRight = BorderStyle.Thin;
            styleCell.BorderTop = BorderStyle.Thin;
            int rowIndex = skipRow;
            int colIndex = 0;
            if(!string.IsNullOrWhiteSpace(title))
            {
                IRow rowtitle = sheet.CreateRow(rowIndex);
                foreach(var pi in mapper)
                {
                    ICell cell = rowtitle.CreateCell(colIndex);
                    colIndex++;
                }
                rowIndex++;
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, colIndex - 1);
                sheet.AddMergedRegion(region);
                rowtitle.GetCell(0).SetCellValue(title);
                var style = sheet.Workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                var f = sheet.Workbook.CreateFont();
                f.IsBold = true;
                //f.Boldweight = (short)FontBoldWeight.Bold;
                f.FontHeight = 16 * 20;
                style.SetFont(f);
                rowtitle.Height = 20 * 20;
                rowtitle.GetCell(0).CellStyle = style;
            }
            IRow row = sheet.CreateRow(rowIndex);
            rowIndex++;
            colIndex = 0;
            foreach(var item in mapper)
            {
                var cell = row.CreateCell(colIndex);
                cell.CellStyle = styleCell;
                cell.SetCellType(CellType.String);
                cell.SetCellValue(item.Value);
                colIndex++;
            }
            sheet.CreateFreezePane(0, 1);

            action(sheet, ref rowIndex, styleCell);

            callback?.Invoke(new SheetInfo
            {
                Sheet = sheet,
                RowStart = 0,
                ColumnStart = 0,
                RowEnd = rowIndex - 1,
                ColumnEnd = colIndex - 1
            });
        }

        #endregion

        #region DataSet

        /// <summary>
        /// 将数据集保存到Excel文件，DataTable对应Sheet，版本格式根据文件名后缀判断
        /// </summary>
        /// <param name="set">数据集</param>
        /// <param name="fileName">保存文件名</param>
        /// <param name="mappers">table 列名称转换</param>
        /// <param name="showSheetHeadTitle">是否需要在每个sheet页中第一行加上标题，标题为tablename</param>
        /// <param name="callbacks">回调函数列表，按顺序对应set中的table，每一个都可以为空</param>
        /// <returns></returns>
        public static string SaveToExcel(DataSet set, string fileName, IDictionary<string, IDictionary<string, string>> mappers, bool showSheetHeadTitle = true, List<Action<SheetInfo>> callbacks = null)
        {
            var version = fileName.EndsWith(".xls", StringComparison.OrdinalIgnoreCase) ? ExcelVersion.Xls : ExcelVersion.Xlsx;
            var book = SaveToWorkbook(set, mappers, version, showSheetHeadTitle, callbacks);
            set.Clear();
            set.Dispose();
            set = null;
            mappers?.Clear();
            mappers = null;
            callbacks?.Clear();
            callbacks = null;
            return SaveBook(fileName, book);
        }

        /// <summary>
        /// 将数据集保存到Excel流中，DataTable对应Sheet
        /// </summary>
        /// <param name="set">数据集</param>
        /// <param name="version">excel版本</param>
        /// <param name="showSheetHeadTitle">是否需要在每个sheet页中第一行加上标题，标题为tablename</param>
        /// <param name="callbacks">回调函数列表，按顺序对应set中的table，每一个都可以为空</param>
        /// <returns></returns>
        public static MemoryStream SaveToExcel(DataSet set, IDictionary<string, IDictionary<string, string>> mappers, ExcelVersion version = ExcelVersion.Xlsx, bool showSheetHeadTitle = true, List<Action<SheetInfo>> callbacks = null)
        {
            var book = SaveToWorkbook(set, mappers, version, showSheetHeadTitle, callbacks);
            set.Clear();
            set.Dispose();
            set = null;
            mappers?.Clear();
            mappers = null;
            callbacks?.Clear();
            callbacks = null;
            return SaveBook(book);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="set"></param>
        /// <param name="mappers"></param>
        /// <param name="version"></param>
        /// <param name="showSheetHeadTitle"></param>
        /// <param name="callbacks"></param>
        /// <returns></returns>
        /// <exception cref="IOException"></exception>
        private static IWorkbook SaveToWorkbook(DataSet set, IDictionary<string, IDictionary<string, string>> mappers, ExcelVersion version = ExcelVersion.Xlsx, bool showSheetHeadTitle = true, List<Action<SheetInfo>> callbacks = null)
        {
            if(set == null)
            {
                throw new IOException("数据集不存在");
            }

            if(set.Tables.Count == 0)
            {
                throw new IOException("不存在可以导出的表");
            }

            if(mappers == null || mappers.Count == 0)
            {
                mappers = new Dictionary<string, IDictionary<string, string>>();
            }

            IWorkbook book = CreateWorkbook(version);
            int index = 1;
            foreach(DataTable table in set.Tables)
            {
                IDictionary<string, string> mapper;
                var tableName = string.IsNullOrWhiteSpace(table.TableName) ? ("sheet" + index) : table.TableName;
                mappers.TryGetValue(tableName, out mapper);
                ISheet sheet = book.CreateSheet(tableName);
                if(table.Columns.Count == 0)
                {
                    var cell = sheet.CreateRow(0).CreateCell(0);
                    cell.SetCellValue(tableName + "不存在可以导出的列");
                    cell.CellStyle.FillForegroundColor = HSSFColor.Red.Index;
                }
                else if(table.Rows.Count == 0)
                {
                    var cell = sheet.CreateRow(0).CreateCell(0);
                    cell.SetCellValue(tableName + "不存在可以导出的行");
                    cell.CellStyle.FillForegroundColor = HSSFColor.Red.Index;
                }
                else
                {
                    Action<SheetInfo> cb = null;
                    if(callbacks != null && callbacks.Count > index - 1)
                    {
                        cb = callbacks[index - 1];
                    }
                    SaveToSheet(table, sheet, mapper, showSheetHeadTitle ? table.TableName : null, cb);
                }

                index++;
            }
            GC.Collect();
            return book;
        }

        #endregion

        delegate void DataAction(ISheet sheet, ref int rowIndex, ICellStyle cellStyle);

        private static IWorkbook CreateWorkbook(ExcelVersion version)
        {
            IWorkbook book;
            if(version == ExcelVersion.Xls)
            {
                book = new HSSFWorkbook();
            }
            else
            {
                book = new SXSSFWorkbook();
            }

            return book;
        }

        private static MemoryStream SaveBook(IWorkbook book)
        {
            using(var ms = new MemoryStream())
            {
                book.Write(ms);
                book.Close();
                GC.Collect();
                return ms;
            }
        }

        private static string SaveBook(string fileName, IWorkbook book)
        {
            using(var ms = new MemoryStream())
            {
                book.Write(ms);

                using(FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] buffer = ms.ToArray();
                    fs.Write(buffer, 0, buffer.Length);
                    fs.Flush();
                }

                ms.Close();
                book.Close();
                GC.Collect();
                return fileName;
            }
        }

        private static bool IsDoubleType(Type type)
        {
            return type == typeof(decimal) ||
                   type == typeof(double) ||
                   type == typeof(float) ||
                   type == typeof(long) ||
                   type == typeof(int) ||
                   type == typeof(short) ||
                   type == typeof(byte) ||
                   type == typeof(ulong) ||
                   type == typeof(uint) ||
                   type == typeof(ushort) ||
                   type == typeof(sbyte);
        }
        private class Mapper
        {
            public string Name { get; set; }
            public string ColumnName { get; set; }
            public Type Type { get; set; }
            public PropertyInfo Property { get; set; }
        }

        /// <summary>
        /// 获取Excel格式对应的媒体格式
        /// </summary>
        /// <param name="version"></param>
        /// <returns></returns>
        public static string GetMineType(ExcelVersion version)
        {
            switch(version)
            {
                case ExcelVersion.Xls:
                    return "application/vnd.ms-excel";
                default:
                    return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            }
        }

        #region LoadFromExcel

        /// <summary>
        /// 从Excel中加载数据（泛型）
        /// </summary>
        /// <typeparam name="T">每行数据的类型</typeparam>
        /// <param name="filePath">Excel文件名</param>
        /// <param name="colMap">列头和实体属性的映射</param>
        /// <param name="sheetIndex">读取的sheet序号</param>
        /// <param name="headerRowCount">标题行数</param>
        /// <returns>泛型列表</returns>
        public static IXSheet<T> LoadFromExcel<T>(string filePath, List<string> colMap = null,
            int sheetIndex = 0,
            int headerRowCount = 1) where T : new()
        {
            if(!File.Exists(filePath))
            {
                throw new IOException("文件不存在");
            }

            using(var fs = File.OpenRead(filePath))
            {
                var book = WorkbookFactory.Create(fs);

                return LoadFromExcel<T>(book, colMap, sheetIndex, headerRowCount);
            }
        }
        /// <summary>
        /// 从Excel中加载数据（泛型）
        /// </summary>
        /// <typeparam name="T">每行数据的类型</typeparam>
        /// <param name="book">excel工作薄</param>
        /// <param name="colMap">列头和实体属性的映射</param>
        /// <param name="sheetIndex">读取的sheet序号</param>
        /// <param name="headerRowCount">标题行数</param>
        /// <returns>泛型列表</returns>
        public static IXSheet<T> LoadFromExcel<T>(IWorkbook book, List<string> colMap = null,
            int sheetIndex = 0,
            int headerRowCount = 1) where T : new()
        {
            Dictionary<string, int> dictHeader = new Dictionary<string, int>();

            try
            {
                var sheet = book.GetSheetAt(sheetIndex);

                int rowStart = sheet.FirstRowNum; //工作区开始行号
                int rowEnd = sheet.LastRowNum; //工作区结束行号

                var headerRow = sheet.GetRow(rowStart + headerRowCount - 1);
                int colStart = headerRow.FirstCellNum; //工作区开始列
                int colEnd = headerRow.LastCellNum; //工作区结束列

                //将每列标题添加到字典中
                for(int i = colStart; i <= colEnd; i++)
                {
                    var colName = headerRow.GetCell(i)?.StringCellValue;
                    if(!string.IsNullOrWhiteSpace(colName))
                    {
                        dictHeader[colName] = i;
                    }
                }

                if(colMap == null)
                {
                    colMap = dictHeader.Keys.ToList();
                }
                else
                {
                    colMap = colMap.Where(x => x != null).ToList();
                    if(dictHeader.Count() != colMap.Count())
                    {
                        var list = string.Join(",", colMap.Except(dictHeader.Keys));
                        throw new Exception($"文件SHEET{sheetIndex}缺少指定列：{list}");
                    }
                }

                IXSheet<T> isheet = new NpoiSheet<T>(sheetIndex, sheet.SheetName, dictHeader);
                //如果有效的列映射为空，则返回空列表
                if(!dictHeader.Any())
                {
                    return isheet;
                }

                for(int rowIndex = rowStart + headerRowCount; rowIndex <= rowEnd; rowIndex++)
                {
                    T result = new T();
                    var hasValue = false; //如果每一列都没有值，忽略这一行

                    var row = sheet.GetRow(rowIndex);
                    if(row == null)
                    {
                        continue;
                    }

                    var irow = new NpoiRow<T>(rowIndex, isheet);
                    //为对象T的各属性赋值
                    foreach(var kvp in dictHeader)
                    {
                        ICell cell = row.GetCell(kvp.Value); //与属性名对应的单元格
                        if(cell == null)
                        {
                            continue;
                        }

                        if(cell.CellType != CellType.Blank)
                        {
                            hasValue = true; //这一行至少有一个值时才添加
                        }
                        var icell = new NpoiCell(kvp.Key, cell);
                        irow.Add(icell);
                    }

                    if(hasValue)
                    {
                        isheet.Add(irow);
                    }
                }

                return isheet;
            }
            finally
            {
                GC.Collect();
            }
        }



        /// <summary>
        /// 从Excel中加载数据（泛型）
        /// </summary>
        /// <typeparam name="T">每行数据的类型</typeparam>
        /// <param name="filePath">Excel文件名</param>
        /// <param name="colMap">列头和实体属性的映射</param>
        /// <param name="sheetIndex">读取的sheet序号</param>
        /// <param name="headerRowCount">标题行数</param>
        /// <param name="after">读取实体后对实体的操作</param>
        /// <returns>泛型列表</returns>
        public static XList<T> LoadFromExcel<T>(string filePath,
            Dictionary<string, Action<T, IXCell>> colMap,
            int sheetIndex = 0,
            int headerRowCount = 1,
            Action<T> after = null) where T : new()
        {
            if(!File.Exists(filePath))
            {
                throw new IOException("文件不存在");
            }

            using(var fs = File.OpenRead(filePath))
            {
                var book = WorkbookFactory.Create(fs);

                return LoadFromExcel(book, colMap, sheetIndex, headerRowCount, after);
            }
        }

        /// <summary>
        /// 判断excel中是否含有某些列
        /// </summary>
        /// <param name="fileName">Excel文件名</param>
        /// <param name="includeColumns">需要确定含有的列名</param>
        /// <param name="sheetIndex">读取的sheet序号</param>
        /// <param name="headerRowCount">标题行数</param>
        /// <param name="expColumns">不能包含的列名</param>
        /// <returns></returns>
        public static bool IsExcelContainColumns(string fileName, List<string> includeColumns, int sheetIndex = 0, int headerRowCount = 1, List<string> expColumns = null)
        {
            if(!File.Exists(fileName))
            {
                throw new IOException("文件不存在");
            }

            using(var fs = File.OpenRead(fileName))
            {
                var book = WorkbookFactory.Create(fs);
                return IsExcelContainColumns(book, includeColumns, sheetIndex, headerRowCount, expColumns);
            }
        }

        /// <summary>
        /// 从Excel中加载数据（泛型）
        /// </summary>
        /// <typeparam name="T">每行数据的类型</typeparam>
        /// <param name="book">Excel文件中workbook</param>
        /// <param name="colMap">列头和实体属性的映射</param>
        /// <param name="sheetIndex">读取的sheet序号</param>
        /// <param name="headerRowCount">标题行数</param>
        /// <param name="after">读取实体后对实体的操作</param>
        /// <returns>泛型列表</returns>
        public static XList<T> LoadFromExcel<T>(IWorkbook book,
            Dictionary<string, Action<T, IXCell>> colMap,
            int sheetIndex = 0,
            int headerRowCount = 1,
            Action<T> after = null) where T : new()
        {
            XList<T> resultList = new XList<T>();
            Dictionary<string, int> dictHeader = new Dictionary<string, int>();

            try
            {
                var sheet = book.GetSheetAt(sheetIndex);

                int rowStart = sheet.FirstRowNum; //工作区开始行号
                int rowEnd = sheet.LastRowNum; //工作区结束行号

                var headerRow = sheet.GetRow(rowStart + headerRowCount - 1);
                int colStart = headerRow.FirstCellNum; //工作区开始列
                int colEnd = headerRow.LastCellNum; //工作区结束列

                if(colMap == null)
                {
                    var properties = typeof(T).GetProperties().ToList();
                    colMap = properties.ToDictionary<PropertyInfo, string, Action<T, IXCell>>(x => x.Name,
                        x => ((m, c) => x.SetValue(m, c.GetValue<object>())));
                }
                else
                {
                    colMap = colMap.Where(x => x.Value != null).ToDictionary(x => x.Key, x => x.Value);
                }

                //将每列标题添加到字典中
                for(int i = colStart; i <= colEnd; i++)
                {
                    var colName = headerRow.GetCell(i)?.StringCellValue;
                    if(!string.IsNullOrWhiteSpace(colName) && colMap.ContainsKey(colName))
                    {
                        dictHeader[colName] = i;
                    }
                }

                //如果有效的列映射为空，则返回空列表
                if(!dictHeader.Any())
                {
                    return resultList;
                }

                for(int rowIndex = rowStart + headerRowCount; rowIndex <= rowEnd; rowIndex++)
                {
                    T result = new T();
                    var hasValue = false; //如果每一列都没有值，忽略这一行
                    var hasError = false; //如果任意一列都有错，忽略这一行

                    var row = sheet.GetRow(rowIndex);
                    if(row == null)
                    {
                        continue;
                    }

                    //为对象T的各属性赋值
                    foreach(var kvp in dictHeader)
                    {
                        try
                        {
                            ICell cell = row.GetCell(kvp.Value); //与属性名对应的单元格
                            if(cell == null)
                            {
                                continue;
                            }

                            if(cell.CellType != CellType.Blank)
                            {
                                hasValue = true; //这一行至少有一个值时才添加
                            }
                            var pName = colMap[kvp.Key];
                            pName?.Invoke(result, new NpoiCell(cell));
                        }
                        catch(Exception ex)
                        {
                            hasError = true;
                            var e = new Exception($"第{rowIndex}行第{kvp.Value}列转换错误：" + ex.Message, ex);
                            resultList.AddException(e);
                        }
                    }

                    if(hasValue && !hasError)
                    {
                        after?.Invoke(result);
                        resultList.Add(result);
                    }
                }

                return resultList;
            }
            catch(Exception ex)
            {
                throw new IOException("选中的文件格式与模版不匹配。", ex);
            }
            finally
            {
                GC.Collect();
            }
        }

        /// <summary>
        /// 判断excel中是否含有某些列
        /// </summary>
        /// <param name="book">Excel文件中workbook</param>
        /// <param name="includeColumns">需要确定含有的列名</param>
        /// <param name="sheetIndex">读取的sheet序号</param>
        /// <param name="headerRowCount">标题行数</param>
        /// <param name="expColumns">不能包含的列名</param>
        /// <returns></returns>
        public static bool IsExcelContainColumns(IWorkbook book, List<string> includeColumns, int sheetIndex = 0, int headerRowCount = 1, List<string> expColumns = null)
        {
            List<string> excelColumn = new List<string>();
            try
            {
                var sheet = book.GetSheetAt(sheetIndex);

                int rowStart = sheet.FirstRowNum; //工作区开始行号
                int rowEnd = sheet.LastRowNum; //工作区结束行号

                var headerRow = sheet.GetRow(rowStart + headerRowCount - 1);
                int colStart = headerRow.FirstCellNum; //工作区开始列
                int colEnd = headerRow.LastCellNum; //工作区结束列

                //将每列标题添加到字典中
                for(int i = colStart; i <= colEnd; i++)
                {
                    var colName = headerRow.GetCell(i)?.StringCellValue;
                    if(!string.IsNullOrWhiteSpace(colName))
                    {
                        excelColumn.Add(colName);
                    }
                }

                if(excelColumn.Any())
                {
                    foreach(var item in includeColumns)
                    {
                        if(!excelColumn.Contains(item))
                        {
                            return false;
                        }
                    }
                }
                if(expColumns != null && expColumns.Any())
                {
                    foreach(var item in expColumns)
                    {
                        if(excelColumn.Contains(item))
                        {
                            return false;
                        }
                    }
                }
                return true;
            }
            catch(Exception ex)
            {
                throw new IOException("选中的文件格式与模版不匹配。", ex);
            }
            finally
            {
                GC.Collect();
            }
        }

        /// <summary>
        /// 从Excel中获取单元格数据  
        /// </summary>    
        /// <param name="filePath">Excel文件名</param> 
        /// <param name="sheetIndex">读取的sheet序号</param>
        /// <param name="rowIndex">行索引</param>
        /// <param name="colIndex">列索引</param>
        /// <returns>具体单元格内容</returns>
        public static string LoadFromExcelCell(string filePath, int sheetIndex = 0, int rowIndex = 0, int colIndex = 0)
        {
            if(!File.Exists(filePath))
            {
                throw new IOException("文件不存在");
            }

            using(var fs = File.OpenRead(filePath))
            {
                var book = WorkbookFactory.Create(fs);
                try
                {
                    var sheet = book.GetSheetAt(sheetIndex);
                    var row = sheet?.GetRow(rowIndex);
                    var col = row?.GetCell(colIndex);
                    return col?.StringCellValue;
                }
                catch(Exception e)
                {
                    throw new IOException("单元格读取失败", e);
                }
                finally
                {
                    GC.Collect();
                }
            }
        }

        #endregion
    }
}